﻿using System;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Windows.Forms;
using myFunction.UserSettings;
using MyFunction.dbFuncion;
using MyFunction.MsSqlHelper;


namespace dataDict
{
    /// <summary>
    ///  窗体：本步数据与SQL服务器间同步数据
    ///  没测试
    /// </summary>
    public partial class syncform : Form
    {
        /// <summary>
        /// 服務器名
        /// </summary>
        private string ServerName; //服務器名
        /// <summary>
        /// 數據庫名
        /// </summary>
        private string Database;   //數據庫名
        /// <summary>
        /// sql server 登录用戶
        /// </summary>
        private string UserName;   //用戶
        /// <summary>
        ///  sql server 登录密码
        /// </summary>
        private string Password; //密碼
        /// <summary>
        ///  sql server 联接字串
        /// </summary>
        private string ConnectionStr; //數據庫聯接字串
        /// <summary>
        ///  sql server 版本
        /// </summary>
        private int SqlVersion;
        /// <summary>
        ///  用户设定集合
        /// </summary>
        private myUserSetting mySetting = new myUserSetting ( );
        /// <summary>
        ///  数据库类实例
        /// </summary>
        MsSqlHelper Helper = null;
        /// <summary>
        /// 數據庫聯接對象
        /// </summary>
        private SqlConnection sqlConnection; //數據庫聯接對象

        /// <summary>
        ///  窗体构造函数
        /// </summary>
        public syncform ( )
        {
            InitializeComponent ( );
        }

        /// <summary>
        ///  窗体载入事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void mainform_Load ( object sender , EventArgs e )
        {

            textBoxServerName.Text = mySetting.DefaultServer;
            //comboBoxDatabase.Text = mySetting.DefaultDatabase;
            textBoxUserName.Text = mySetting.DefaultUser;
        }


        #region 进入数据库下拉列表，刷新下拉列表的内容
        /// <summary>
        ///  事件： comboBoxDatabase 进入刷新数据名称列表
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void comboBoxDatabase_Enter ( object sender , EventArgs e )
        {
            ServerName = textBoxServerName.Text.Trim ( );
            Database = "master";
            UserName = textBoxUserName.Text.Trim ( );
            Password = textBoxPassword.Text.Trim ( );

            ConnectionStr = @"Data Source=" + ServerName
                + ";Initial Catalog=" + Database +
                ";Persist Security Info=True;User ID="
                + UserName + ";Password=" + Password;
            // 测试服务器参数是否正确
            try
            {
                sqlConnection = new SqlConnection ( ConnectionStr );
                sqlConnection.Open ( );
                sqlConnection.Close ( );
                string cmdText = "SELECT Name FROM Master..SysDatabases ORDER BY Name";
                Helper = new MsSqlHelper ( ConnectionStr );
                if (Helper.Load ( cmdText ))
                {
                    comboBoxDatabase.Items.Clear ( );
                    DataTable dt = Helper.DataSet.Tables[0];
                    int i = 0;
                    foreach (DataRow dr in dt.Rows)
                    {
                        comboBoxDatabase.Items.Add ( dr[0] );
                        if (dr[0].ToString ( ).Trim ( ) == mySetting.DefaultDatabase)
                        {
                            comboBoxDatabase.SelectedIndex = i;
                        }
                        i++;
                    }
                    textBoxVersion.Text = GetSqlVer ( ConnectionStr );
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show ( ex.Message , Text , MessageBoxButtons.OK , MessageBoxIcon.Information );
            }


        }
        #endregion


        #region 取数据库版本
        /// <summary>
        ///  方法：取数据库版本号
        /// </summary>
        /// <param name="conStr"></param>
        /// <returns></returns>
        public string GetSqlVer ( string conStr )
        {
            string cmdText = "SELECT  SERVERPROPERTY('productversion') AS versionid ," +
                    "SERVERPROPERTY('productlevel') AS sp_id ," +
                   "SERVERPROPERTY('edition') AS version";
            Helper = new MsSqlHelper ( conStr );
            string SqlVer;
            int mainVersonId = 0;
            if (Helper.Load ( cmdText ))
            {
                string version_id = Helper.DataSet.Tables[0].Rows[0][0].ToString ( );
                int.TryParse ( version_id.Substring ( 0 , version_id.IndexOf ( "." ) ) , out mainVersonId );
            }
            SqlVersion = mainVersonId;
            switch (mainVersonId)
            {
                case 8:
                    SqlVer = "SQL 2000";
                    break;
                case 9:
                    SqlVer = "SQL 2005";
                    break;
                case 10:
                    SqlVer = "SQL 2008";
                    break;
                case 11:
                    SqlVer = "SQL 2012";
                    break;
                default:
                    SqlVer = "其他SQL版本";
                    break;
            }
            return SqlVer;
        }
        #endregion

        #region 退出菜单
        /// <summary>
        ///  菜单：保存用户设定，关闭窗体
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void toolStripExit_Click ( object sender , EventArgs e )
        {
            mySetting.DefaultUser = textBoxUserName.Text;
            mySetting.DefaultServer = textBoxServerName.Text;
            mySetting.DefaultDatabase = comboBoxDatabase.Text;
            mySetting.Save ( );
            Close ( );
        }
        #endregion

        #region 同步按钮
        /// <summary>
        ///  按钮：调用SyncData 方法，同步本地与服务器间的数据
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void toolStripSync_Click ( object sender , EventArgs e )
        {
            if (string.IsNullOrEmpty ( ConnectionStr ))
            {
                return; // 没有联接字串，返回
            }
            Database = comboBoxDatabase.Text; //重新设定数据库
            SyncData ( ); //同步数据
        }
        #endregion

        #region 同步数据
        /// <summary>
        ///  方法：调用bgwSyncTable.RunWorkerAsync 同步资料
        /// </summary>
        private void SyncData ( )
        {
            ConnectionStr = @"Data Source=" + ServerName
            + ";Initial Catalog=" + Database +
            ";Persist Security Info=True;User ID="
            + UserName + ";Password=" + Password;
            bgwSyncTable.RunWorkerAsync ( );

        }
        #endregion

        #region 同步表名资料
        /// <summary>
        ///  方法：同步表名资料
        /// </summary>
        /// <param name="worker"></param>
        /// <param name="e"></param>
        private void SyncTableName ( BackgroundWorker worker , DoWorkEventArgs e )
        {
            string cmdText = "";

            switch (SqlVersion)
            {
                case 8: // sql 2000
                    cmdText = sqlTableNameText ( );
                    break;
                case 9: //sql2005
                    cmdText = sqlTableNameText2008();
                    break;
                case 10: // sql2008
                    cmdText = sqlTableNameText2008 ( );
                    break;
                case 11: // sql2012
                    cmdText = sqlTableNameText2008();
                    break;
                default:
                    break;
            }

            Helper = new MsSqlHelper ( ConnectionStr );
            if (!Helper.Load ( cmdText ))
            {
                //LabelMessage.Text = "同步失败！";
                //LabelMessage.ForeColor = Color.Red;
                return;
            }
            worker.ReportProgress ( 0 , "同步表名数据" );
            DataTable ServerData = Helper.DataSet.Tables[0]; //数据库的资料
            u_systableTableAdapter1.FillByDatabase ( dataSet11.u_systable , Database ); //本地的资料

            string TableName;
            DataTable NativeData = dataSet11.u_systable;
            DataRow[] OptRows;

            string nowdate = DateTime.Now.ToString ( "yyyy/MM/dd HH:mm" );
            #region 同步，添加服務器的新項目,將本地修改的內容更新到服務器。
            int TableCount = ServerData.Rows.Count;
            int idx = 0; //进度索引
            foreach (DataRow dr in ServerData.Rows)
            {
                TableName = dr["name"].ToString ( );
                OptRows = NativeData.Select ( "table_name='" + TableName + "'" );
                DataRow optrow = null;
                if (OptRows.Length > 0) //有數據
                {
                    idx++;
                    optrow = OptRows[0];
                    DateTime Lastupdate , SyncDate;
                    string TableComment = optrow["table_comment"] == null ? "" : optrow["table_comment"].ToString ( );
                    Lastupdate = DateTime.Parse ( optrow["lastupdate"].ToString ( ) );
                    SyncDate = DateTime.Parse ( optrow["sync_date"].ToString ( ) );
                    if (Lastupdate > SyncDate)
                    {
                        //更新服务器上的表描述
                        if (!string.IsNullOrEmpty ( TableComment ))
                        {
                            UpdateTableComment ( TableName , "MS_Description" , optrow["table_comment"].ToString ( ) );
                        }
                        //项目名
                        if (!string.IsNullOrEmpty ( optrow["sys_id"].ToString ( ) ))
                        {
                            UpdateTableComment ( TableName , "U_ProjectId" , optrow["sys_id"].ToString ( ) );
                        }
                        // 项目子分类
                        if (!string.IsNullOrEmpty ( optrow["collates"].ToString ( ) ))
                        {
                            UpdateTableComment ( TableName , "U_Collates" , optrow["collates"].ToString ( ) );
                        }
                    }
                    else
                    {
                        optrow["table_comment"] = dr["table_comment"];
                        optrow["sys_id"] = dr["sys_id"];
                        optrow["collates"] = dr["collates"];
                    }
                    optrow["sync_date"] = nowdate;

                }
                else //找不到本地数据，添加数据
                {
                    optrow = NativeData.NewRow ( );
                    optrow["database"] = Database;
                    optrow["table_name"] = TableName;
                    optrow["table_comment"] = dr["table_comment"];
                    optrow["sys_id"] = dr["sys_id"];
                    optrow["collates"] = dr["collates"];
                    optrow["lastupdate"] = nowdate;
                    optrow["sync_date"] = nowdate;
                    NativeData.Rows.Add ( optrow );
                }
                worker.ReportProgress ( (100 * idx) / TableCount , "同步表名数据" );
            }
            #endregion

            #region 清理本地无用数据
            idx = 0;
            string sqliteConnetionStr = dBFunctions.ConnectionStringSQLite;
            SQLiteConnection sqliteConn = new SQLiteConnection ( sqliteConnetionStr );
            sqliteConn.Open ( );
            DbTransaction trans = sqliteConn.BeginTransaction ( );
            try
            {
                TableCount = dataSet11.u_systable.Rows.Count;
                worker.ReportProgress ( (100 * idx) / TableCount , "清除本地无用数据" );

                for (int i = dataSet11.u_systable.Rows.Count - 1; i >= 0; i--)
                {
                    idx++;
                    DataRow dr = dataSet11.u_systable.Rows[i];
                    TableName = dr["table_name"].ToString ( );
                    OptRows = ServerData.Select ( "name ='" + TableName + "'" );
                    if (OptRows.Length == 0)
                    {
                        string cmdTextDelete = "delete from u_systable where database ='" + Database + "'and table_name='" + TableName + "' ;";
                        cmdTextDelete += " delete from u_syscolcomment where database ='" + Database + "'and table_name='" + TableName + "' ;";
                        SQLiteCommand myCommand = new SQLiteCommand ( cmdTextDelete , sqliteConn );
                        myCommand.ExecuteNonQuery ( );
                    }
                    else
                    {
                        string cmdRelace = "replace  into u_systable (database,sys_id,collates,table_name,table_comment,lastupdate,sync_date) values(@database,@sys_id,@collates,@table_name,@table_comment,@lastupdate,@sync_date)";
                        SQLiteCommand myCommand = new SQLiteCommand ( cmdRelace , sqliteConn );
                        myCommand.Parameters.Add ( "@database" , DbType.String ).Value = Database;
                        myCommand.Parameters.Add ( "@sys_id" , DbType.String ).Value = dr["sys_id"].ToString ( );
                        myCommand.Parameters.Add ( "@collates" , DbType.String ).Value = dr["collates"].ToString ( );
                        myCommand.Parameters.Add ( "@table_name" , DbType.String ).Value = dr["table_name"].ToString ( );
                        myCommand.Parameters.Add ( "@table_comment" , DbType.String ).Value = dr["table_comment"].ToString ( );
                        myCommand.Parameters.Add ( "@lastupdate" , DbType.DateTime ).Value = DateTime.Parse ( dr["lastupdate"].ToString ( ) );
                        myCommand.Parameters.Add ( "@sync_date" , DbType.DateTime ).Value = DateTime.Parse ( dr["sync_date"].ToString ( ) );

                        myCommand.ExecuteNonQuery ( );
                        //@collates,@table_name,@table_comment,@lastupdate,@sync_date
                    }

                    worker.ReportProgress ( (100 * idx) / TableCount , "清除本地无用数据" );
                }
                trans.Commit ( );
                worker.ReportProgress ( 100 , "表数据同步完成" );
            }
            catch (System.Exception ex)
            {
                trans.Rollback ( );
                worker.ReportProgress ( 0 , ex.Message );
            }
            finally
            {
                sqliteConn.Clone ( );
            }

            #endregion
        }
        #endregion

        #region 同步字段数据
        /// <summary>
        ///  同步字段数据
        /// </summary>
        /// <param name="worker"></param>
        /// <param name="e"></param>
        public void SyncTableField ( BackgroundWorker worker , DoWorkEventArgs e )
        {
            //同步日期
            string nowdate = DateTime.Now.ToString ( "yyyy/MM/dd HH:mm" );
            string cmdText = "";
            switch (SqlVersion)
            {
                case 8:
                    cmdText = sql2000field ( );
                    break;
                case 9:
                    cmdText = sql2008field();
                    break;
                case 10:
                    cmdText = sql2008field ( );
                    break;
                case 11:
                    cmdText = sql2008field();
                    break;
                default:
                    break;
            }

            MsSqlHelper FieldHelper = new MsSqlHelper ( ConnectionStr );
            u_syscolcommentTableAdapter1.FillByDatabase ( dataSet11.u_syscolcomment , Database );
            string TableName , FieldName , FiledComment;
            DataTable NativeTable = dataSet11.u_syscolcomment;
            if (!FieldHelper.Load ( cmdText ))
            {
                return;
            }
            DataTable FieldTable = FieldHelper.DataSet.Tables[0];
            int FieldCount = FieldTable.Rows.Count;
            int idx = 0;
            worker.ReportProgress ( (100 * idx) / FieldCount , "同步字段数据" );
            DataRow[] OptRows = null;
            DataRow optrow = null;
            #region 服务器与本地数据同步
            foreach (DataRow dr in FieldTable.Rows)
            {
                idx++;
                TableName = dr["table_name"].ToString ( );
                FieldName = dr["field_name"].ToString ( );
                OptRows = NativeTable.Select ( "table_name ='" + TableName + "' and field_name = '" + FieldName + "'" );
                if (OptRows.Length > 0)
                {
                    optrow = OptRows[0];
                    DateTime Lastupdate , SyncDate;
                    if (!string.IsNullOrEmpty ( optrow["lastupdate"].ToString ( ) ))
                    {
                        Lastupdate = DateTime.Parse ( optrow["lastupdate"].ToString ( ) );
                        SyncDate = DateTime.Parse ( optrow["sync_date"].ToString ( ) );
                        if (Lastupdate > SyncDate)
                        {
                            //更新服务器上的内容
                            FiledComment = optrow["field_desc"].ToString ( ).Trim ( );
                            UpdateColumnComment ( TableName , FieldName , FiledComment );
                        }
                    }
                    else
                    {
                        optrow["field_item"] = dr["item_no"];
                        optrow["data_type"] = dr["data_type"];
                        optrow["is_primary_key"] = dr["is_primary_key"];
                        optrow["field_length"] = dr["field_length"];
                        optrow["xprecision"] = dr["xprecision"];
                        optrow["xscale"] = dr["xscale"];
                        optrow["is_allow_null"] = dr["is_allow_null"];
                        optrow["is_identity"] = dr["is_identity"];
                        optrow["default_value"] = dr["default_value"];
                        optrow["field_desc"] = dr["field_desc"];
                    }
                    optrow["sync_date"] = nowdate;
                }
                else
                {
                    optrow = NativeTable.NewRow ( );
                    optrow["database"] = Database;
                    optrow["table_name"] = dr["table_name"];
                    optrow["field_item"] = dr["item_no"];
                    optrow["field_name"] = dr["field_name"];
                    optrow["data_type"] = dr["data_type"];
                    optrow["is_primary_key"] = dr["is_primary_key"];
                    optrow["field_length"] = dr["field_length"];
                    optrow["xprecision"] = dr["xprecision"];
                    optrow["xscale"] = dr["xscale"];
                    optrow["is_allow_null"] = dr["is_allow_null"];
                    optrow["is_identity"] = dr["is_identity"];
                    optrow["default_value"] = dr["default_value"];
                    optrow["field_desc"] = dr["field_desc"];
                    optrow["sync_date"] = nowdate;
                    optrow["lastupdate"] = nowdate;
                    NativeTable.Rows.Add ( optrow );
                }
                worker.ReportProgress ( (100 * idx) / FieldCount , "同步字段数据" );
            }
            #endregion


            worker.ReportProgress ( 100 , "正在保存数据" );

            #region 清理本地无用的数据
            string sqliteConnetionStr = dBFunctions.ConnectionStringSQLite;
            SQLiteConnection sqliteConn = new SQLiteConnection ( sqliteConnetionStr );
            sqliteConn.Open ( );
            DbTransaction trans = sqliteConn.BeginTransaction ( );
            try
            {
                idx = 0;
                FieldCount = dataSet11.u_syscolcomment.Rows.Count;
                worker.ReportProgress ( (100 * idx) / FieldCount , "删除本地无用字段数据" );

                for (int i = dataSet11.u_syscolcomment.Rows.Count - 1; i >= 0; i--)
                {
                    //i++;
                    idx++;
                    DataRow dr = dataSet11.u_syscolcomment.Rows[i];
                    TableName = dr["table_name"].ToString ( );
                    FieldName = dr["field_name"].ToString ( );
                    OptRows = FieldTable.Select ( "table_name ='" + TableName + "' and field_name ='" + FieldName + "'" );
                    if (OptRows.Length == 0)
                    {
                        string cmdText1 = " delete from u_syscolcomment where database ='" + Database + "'and table_name='" + TableName + "' and field_name ='" + FieldName + "'";
                        SQLiteCommand myCommand = new SQLiteCommand ( cmdText1 , sqliteConn );
                        int row = myCommand.ExecuteNonQuery ( );
                    }
                    else
                    {
                        #region 更新本地资料

                        string cmdText1 = "replace  into u_syscolcomment (database,table_name,field_item,field_name,data_type,is_primary_key,field_length,xprecision, " +
                            " xscale,is_allow_null,is_identity,default_value,field_desc,lastupdate,sync_date) " +
                            " values(@database,@table_name,@field_item,@field_name,@data_type,@is_primary_key, " +
                            " @field_length,@xprecision,@xscale,@is_allow_null,@is_identity,@default_value, " +
                            " @field_desc,@lastupdate,@sync_date)";
                        SQLiteCommand myCommand = new SQLiteCommand ( cmdText1 , sqliteConn );

                        myCommand.Parameters.Add ( "@database" , DbType.String ).Value = Database;
                        myCommand.Parameters.Add ( "@table_name" , DbType.String ).Value = dr["table_name"].ToString ( );
                        myCommand.Parameters.Add ( "@field_item" , DbType.String ).Value = int.Parse ( dr["field_item"].ToString ( ) );
                        myCommand.Parameters.Add ( "@field_name" , DbType.String ).Value = dr["field_name"].ToString ( );
                        myCommand.Parameters.Add ( "@data_type" , DbType.String ).Value = dr["data_type"].ToString ( );
                        myCommand.Parameters.Add ( "@is_primary_key" , DbType.Int32 ).Value = int.Parse ( dr["is_primary_key"].ToString ( ) );
                        myCommand.Parameters.Add ( "field_length" , DbType.Int32 ).Value = int.Parse ( dr["field_length"].ToString ( ) );
                        myCommand.Parameters.Add ( "@xprecision" , DbType.Int32 ).Value = int.Parse ( dr["xprecision"].ToString ( ) );
                        myCommand.Parameters.Add ( "@xscale" , DbType.Int32 ).Value = int.Parse ( dr["xscale"].ToString ( ) );
                        myCommand.Parameters.Add ( "@is_allow_null" , DbType.Int32 ).Value = int.Parse ( dr["is_allow_null"].ToString ( ) );
                        myCommand.Parameters.Add ( "@is_identity" , DbType.Int32 ).Value = int.Parse ( dr["is_identity"].ToString ( ) );
                        myCommand.Parameters.Add ( "@default_value" , DbType.String ).Value = dr["default_value"].ToString ( );
                        myCommand.Parameters.Add ( "@field_desc" , DbType.String ).Value = dr["field_desc"].ToString ( );
                        myCommand.Parameters.Add ( "@lastupdate" , DbType.DateTime ).Value = DateTime.Parse ( dr["lastupdate"].ToString ( ) );
                        myCommand.Parameters.Add ( "@sync_date" , DbType.DateTime ).Value = DateTime.Parse ( dr["sync_date"].ToString ( ) );
                        int row = myCommand.ExecuteNonQuery ( );
                        #endregion
                    }
                    worker.ReportProgress ( (100 * idx) / FieldCount , "删除本地无用字段数据" );
                }
                trans.Commit ( );

            }
            catch (System.Exception ex)
            {
                trans.Rollback ( );
                worker.ReportProgress ( 0 , ex.Message );
            }
            finally
            {
                sqliteConn.Clone ( );
            }

            #endregion

        }
        #endregion

        #region 将本地的内容更新到服务器上
        /// <summary>
        ///  将本地的内容更新到服务器上，sql 2005 及以上使用
        /// </summary>
        /// <param name="ObjectName">表名</param>
        /// <param name="Typename">列名</param>
        /// <param name="Descript">列描述</param>
        public void UpdateTableComment ( string ObjectName , string Typename , string Descript )
        {
            // 查测扩展属性是否存在，如已存在，要调用 sp_updateextendedproperty 否则调用sp_addextendedproperty
            string cmdText = "  SELECT  * " +
                "FROM  ::fn_listextendedproperty ('" + Typename + "', 'user', 'dbo', 'table', '" + ObjectName + "', null, NULL)    ";
            MsSqlHelper myHelper = new MsSqlHelper ( ConnectionStr );
            myHelper.Load ( cmdText );
            SqlCommand myCommand = null;
            if (myHelper.DataSet.Tables[0].Rows.Count == 0)
            {
                myCommand = new SqlCommand ( "sp_addextendedproperty" , sqlConnection );//调用存储过程名 

            }
            else
            {
                myCommand = new SqlCommand ( "sp_updateextendedproperty" , sqlConnection );
            }
            myCommand.CommandType = CommandType.StoredProcedure;

            //设置存储过程的参数值. 
            SqlParameter CollatesName = myCommand.Parameters.Add ( "@name" , SqlDbType.Char );
            SqlParameter CollatesDesc = myCommand.Parameters.Add ( "@value" , SqlDbType.Char );
            SqlParameter level0type = myCommand.Parameters.Add ( "level0type" , SqlDbType.VarChar );
            SqlParameter level0name = myCommand.Parameters.Add ( "@level0name" , SqlDbType.VarChar );
            SqlParameter level1type = myCommand.Parameters.Add ( "@level1type" , SqlDbType.VarChar );
            SqlParameter level1name = myCommand.Parameters.Add ( "@level1name" , SqlDbType.VarChar );
            SqlParameter level2type = myCommand.Parameters.Add ( "@level2type" , SqlDbType.VarChar );
            SqlParameter level2name = myCommand.Parameters.Add ( "@level2name" , SqlDbType.VarChar );

            CollatesName.Value = Typename;
            CollatesDesc.Value = Descript;
            level0type.Value = "user";
            level0name.Value = "dbo";
            level1type.Value = "table";
            level1name.Value = ObjectName;
            level2type.Value = null;
            level2name.Value = null;

            #region sp_addextendedproperty 参数
            //@name	sysname
            //@value	sql_variant
            //@level0type	varchar(128)
            //@level0name	sysname
            //@level1type	varchar(128)
            //@level1name	sysname
            //@level2type	varchar(128)
            //@level2name	sysname
            #endregion
            myCommand.ExecuteNonQuery ( );
        }

        #endregion

        #region 将本地的字段内容更新到服务器上,有版本之分，只适用于SQL2000
       /// <summary>
        ///  将本地的字段内容更新到服务器上,有版本之分，只适用于SQL2000
       /// </summary>
       /// <param name="tableName">表名</param>
       /// <param name="columnName">列名</param>
       /// <param name="Descript">列描述</param>
        public void UpdateColumnComment ( string tableName , string columnName , string Descript )
        {
            // 查测扩展属性是否存在，如已存在，要调用 sp_updateextendedproperty 否则调用sp_addextendedproperty
            string cmdText = "  SELECT  * " +
                "FROM  ::fn_listextendedproperty ('MS_Description', 'user', 'dbo', 'table', '" + tableName + "','column', '" + columnName + "')   ";
            MsSqlHelper myHelper = new MsSqlHelper ( ConnectionStr );
            myHelper.Load ( cmdText );
            SqlCommand myCommand = null;
            if (myHelper.DataSet.Tables[0].Rows.Count == 0)
            {
                myCommand = new SqlCommand ( "sp_addextendedproperty" , sqlConnection );//调用存储过程名 

            }
            else
            {
                myCommand = new SqlCommand ( "sp_updateextendedproperty" , sqlConnection );
            }



            myCommand.CommandType = CommandType.StoredProcedure;

            //设置存储过程的参数值. 
            SqlParameter CollatesName = myCommand.Parameters.Add ( "@name" , SqlDbType.Char );
            SqlParameter CollatesDesc = myCommand.Parameters.Add ( "@value" , SqlDbType.Char );
            SqlParameter level0type = myCommand.Parameters.Add ( "level0type" , SqlDbType.VarChar );
            SqlParameter level0name = myCommand.Parameters.Add ( "@level0name" , SqlDbType.VarChar );
            SqlParameter level1type = myCommand.Parameters.Add ( "@level1type" , SqlDbType.VarChar );
            SqlParameter level1name = myCommand.Parameters.Add ( "@level1name" , SqlDbType.VarChar );
            SqlParameter level2type = myCommand.Parameters.Add ( "@level2type" , SqlDbType.VarChar );
            SqlParameter level2name = myCommand.Parameters.Add ( "@level2name" , SqlDbType.VarChar );

            CollatesName.Value = "MS_Description";
            CollatesDesc.Value = Descript;
            level0type.Value = "user";
            level0name.Value = "dbo";
            level1type.Value = "table";
            level1name.Value = tableName;
            level2type.Value = "COLUMN";
            level2name.Value = columnName;

            #region sp_addextendedproperty 参数
            //@name	sysname
            //@value	sql_variant
            //@level0type	varchar(128)
            //@level0name	sysname
            //@level1type	varchar(128)
            //@level1name	sysname
            //@level2type	varchar(128)
            //@level2name	sysname
            #endregion
            myCommand.ExecuteNonQuery ( );
        }

        #endregion
        
        #region Sql2000 取表结构语句
        /// <summary>
        /// Sql2000 取表结构语句
        /// </summary>
        /// <returns>Sql2000 取表结构语句</returns>
        public string sql2000field ( )
        {
            return "SELECT  table_name = d.name ," +
            "        item_no = a.colorder ,                                                                      " +
            "        field_name = a.name ,                                                                      " +
            "        data_type = b.name ,                                                                       " +
            "        field_length = a.length ,                                                                  " +
            "        xprecision = COLUMNPROPERTY(a.id, a.name, 'PRECISION') ,                                   " +
            "        xscale = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) ,                                " +
            "        a.isnullable AS is_allow_null ,                                                            " +
            "        default_value = ISNULL(e.text, '') ,                                                       " +
            "        CONVERT (CHAR(100), ISNULL(g.[value], '')) AS field_desc ,                                 " +
            "                                                                                                   " +
            "        Is_Identity = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1                     " +
            "                         THEN 1                                                                    " +
            "                         ELSE 0                                                                    " +
            "                    END ,                                                                          " +
            "        is_primary_key = CASE WHEN EXISTS ( SELECT  1                                              " +
            "                                            FROM    sysobjects                                     " +
            "                                            WHERE   xtype = 'PK'                                   " +
            "                                                    AND parent_obj = a.id                          " +
            "                                                    AND name IN (                                  " +
            "                                                    SELECT  name                                   " +
            "                                                    FROM    sysindexes                             " +
            "                                                    WHERE   indid IN (                             " +
            "                                                            SELECT                                 " +
            "                                                              indid                                " +
            "                                                            FROM                                   " +
            "                                                              sysindexkeys                         " +
            "                                                            WHERE                                  " +
            "                                                              id = a.id                            " +
            "                                                              AND colid = a.colid ) ) )            " +
            "                              THEN 1                                                               " +
            "                              ELSE 0                                                               " +
            "                         END                                                                       " +
            "FROM    syscolumns a                                                                               " +
            "        INNER JOIN systypes b ON a.xusertype = b.xusertype                                         " +
            "        INNER JOIN sysobjects d ON a.id = d.id                                                     " +
            "                                   AND d.xtype = 'U'                                               " +
            "                                   AND d.uid = 1                                                   " +
            "                                   AND d.name <> 'dtproperties'                                    " +
            "        LEFT JOIN syscomments e ON a.cdefault = e.id                                               " +
            "        LEFT JOIN sysproperties g ON a.id = g.id                                                   " +
            "                                     AND a.colid = g.smallid                                       " +
            "        LEFT JOIN sysproperties f ON d.id = f.id                                                   " +
            "                                     AND f.smallid = 0                                             " +
            "WHERE   d.xtype = 'U'                                                                              " +
            "ORDER BY d.name ,                                                                                  " +
            "        item_no                                                                                    ";

        }
        #endregion

        #region Sql2008 取表结构
        /// <summary>
        /// Sql2008 取表结构
        /// </summary>
        /// <returns>Sql2008 取表结构</returns>
        public string sql2008field ( )
        {
            return "SELECT   table_name = d.name , " +
                "item_no = a.colorder , " +
                "field_name = a.name , " +
                "data_type = b.name , " +
                "field_length = a.length , " +
                "xprecision = COLUMNPROPERTY(a.id, a.name, 'PRECISION') , " +
                "xscale = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) , " +
                "a.isnullable AS is_allow_null , " +
                "default_value = ISNULL(e.text, '') , " +
                "CONVERT (CHAR(100), ISNULL(g.[value], '')) AS field_desc , " +
                "Is_Identity = CASE WHEN COLUMNPROPERTY(a.id, a.name, " +
                "                                       'IsIdentity') = 1 " +
                "                   THEN 1 " +
                "                   ELSE 0 " +
                "              END , " +
                "is_primary_key = CASE WHEN EXISTS ( SELECT  1 " +
                "                                    FROM    sysobjects " +
                "                                    WHERE   xtype = 'PK' " +
                "                                            AND parent_obj = a.id " +
                "                                            AND name IN ( " +
                "                                            SELECT " +
                "                                              name " +
                "                                            FROM " +
                "                                              sysindexes " +
                "                                            WHERE " +
                "                                              indid IN ( " +
                "                                              SELECT " +
                "                                              indid " +
                "                                              FROM " +
                "                                              sysindexkeys " +
                "                                              WHERE " +
                "                                              id = a.id " +
                "                                              AND colid = a.colid ) ) ) " +
                "                      THEN 1 " +
                "                      ELSE 0 " +
                "                 END " +
       "FROM     syscolumns a " +
       "        INNER JOIN systypes b ON a.xusertype = b.xusertype " +
       "         INNER JOIN sysobjects d ON a.id = d.id " +
       "                                    AND d.xtype = 'U' " +
       "                                    AND d.uid = 1 " +
       "                                    AND d.name <> 'dtproperties' " +
       "         LEFT JOIN syscomments e ON a.cdefault = e.id " +
       "         LEFT JOIN sys.extended_properties g ON a.id = g.major_id " +
       "                                                AND a.colid = g.minor_id " +
       "         LEFT JOIN sys.extended_properties f ON d.id = f.major_id " +
       "                                                AND f.minor_id = 0 " +
       "WHERE    d.xtype = 'U' " +
       "ORDER BY d.name , " +
                "item_no  ";
        }
        #endregion
        
        #region 取系统的表名及自定义的表描述，表分类
        /// <summary>
        /// 取 sql2000 系统的表名及自定义的表描述，表分类
        /// </summary>
        /// <returns>sql语句</returns>
        public string sqlTableNameText ( )
        {
            return "SELECT  d.name , " +
            "MAX(CASE when a.NAME='MS_Description' THEN a.VALUE  ELSE '' END ) AS table_comment, " +
            "MAX(CASE WHEN a.NAME ='U_ProjectId' THEN ISNULL(a.VALUE,'*')  ELSE '*' END ) AS sys_id, " +
            "MAX(CASE WHEN a.name='U_Collates' THEN ISNULL(a.value,'*') ELSE '*' END) AS collates " +
            "FROM    sysobjects d " +
            "LEFT JOIN sysproperties a ON d.id = a.id " +
            "AND a.type=3 " +
            "WHERE   d.xtype = 'U' " +
            "AND d.uid = 1 " +
            "AND d.name <> 'dtproperties' " +
            "GROUP BY d.name";
        }
        #endregion
        /// <summary>
        /// 取 sql2008 系统的表名及自定义的表描述，表分类
        /// </summary>
        /// <returns>sql 语句</returns>
        public string sqlTableNameText2008 ( )
        {
            return "SELECT  d.name , " +
         "MAX(CASE when a.NAME='MS_Description' THEN a.VALUE ELSE '' END ) AS table_comment, " +
         "MAX(CASE WHEN a.NAME ='U_ProjectId' THEN ISNULL(a.VALUE,'*') ELSE '*' END ) AS sys_id, " +
         "MAX(CASE WHEN a.name='U_Collates' THEN ISNULL(a.value,'*')ELSE '*'  END) AS collates " +
         "FROM    sysobjects d " +
         "LEFT JOIN sys.extended_properties a ON d.id = a.major_id " +
         "AND a.minor_id=0 " +
         "WHERE   d.xtype = 'U' " +
         "AND d.uid = 1 " +
         "AND d.name <> 'dtproperties' " +
         "GROUP BY d.name        ";
        }


        #region 启动后台同步数据
        /// <summary>
        ///  backgroundwork 启动事件：启动后台同步数据SyncTableName、SyncTableField 
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void bgwSyncTable_DoWork ( object sender , System.ComponentModel.DoWorkEventArgs e )
        {
            BackgroundWorker worker = sender as BackgroundWorker;
            try
            {
                #region 测试连接是否正确
                sqlConnection = new SqlConnection ( ConnectionStr );
                sqlConnection.Open ( );
                SyncTableName ( worker , e );
                SyncTableField ( worker , e );
                sqlConnection.Close ( );
                #endregion
            }
            catch (System.Exception ex)
            {
                MessageBox.Show ( ex.Message );
            }
        }
        #endregion

        #region 同步进度
        /// <summary>
        ///  backgroundwork 进度改变
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void bgwSyncTable_ProgressChanged ( object sender , ProgressChangedEventArgs e )
        {
            LabelMessage.Text = e.UserState.ToString ( );
            ProgressBarSync.Value = e.ProgressPercentage;
        }
        #endregion
        #region 同步完报告
        /// <summary>
        ///  backgroundwork 完成事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void bgwSyncTable_RunWorkerCompleted ( object sender , RunWorkerCompletedEventArgs e )
        {

            if (e.Cancelled)
            {
                // The user canceled the operation.
                MessageBox.Show ( "Operation was canceled" );
            }
            else if (e.Error != null)
            {
                // There was an error during the operation.
                string msg = String.Format ( "An error occurred: {0}" , e.Error.Message );
                MessageBox.Show ( msg );
            }
            else
            {
                // The operation completed normally.
                string msg = "同步完成！";
                MessageBox.Show ( msg );
            }


        }
        #endregion

    }
}